/*
 * Copyright 2009 Vladimir Ritz Bossicard
 *
 * This file is part of OraDoc.
 *
 * OraDoc is free software: you can redistribute it and/or modify it under the
 * terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or (at your option) any later
 * version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
 * more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * this program. If not, see <http://www.gnu.org/licenses/>.
 *
 * Version     : $Revision: 20 $
 * Last edit   : $Date: 2009-03-25 12:43:30 +0000 (Wed, 25 Mar 2009) $
 * Last editor : $Author: vbossica $
 */
package org.oradoc.structures.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.BooleanUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 *
 * @author Vladimir Ritz Bossicard
 */
public class ViewDbDao extends JdbcDaoSupport implements ViewDao {

    private final static String ALL_VIEWS_QUERY = "SELECT object_name FROM user_objects WHERE object_type = 'VIEW' ORDER BY object_name";

    private Restrictor restrictor = new NullRestrictor();

    private final static String VIEW_QUERY = "SELECT v.view_name, v.text, com.comments " +
        "FROM user_views v, all_tab_comments com " +
        "WHERE com.owner = (select username from user_users) " +
        "AND v.view_name = com.table_name " +
        "AND v.view_name = ?";

    private final static String VIEW_COLUMNS_QUERY = "SELECT col.owner, col.table_name, col.column_name, col.data_type, col.nullable AS optional, data_default, com.comments " +
        "FROM all_tab_columns col, all_col_comments com " +
        "WHERE col.owner = com.owner " +
        "AND col.table_name = com.table_name " +
        "AND col.column_name = com.column_name " +
        "AND com.owner = (select username from user_users) " +
        "AND col.table_name = ? " +
        "ORDER BY col.column_id";

    public void setRestrictor(final Restrictor restrictor) {
        this.restrictor = restrictor;
    }

    @SuppressWarnings("unchecked")
    public List<String> getAllViewNames() {
        List<String> names = getJdbcTemplate().queryForList(ALL_VIEWS_QUERY, null, String.class);
        List<String> res = new ArrayList<String>();
        for (String name : names) {
            if (this.restrictor.isIncluded(name)) {
                res.add(name);
            }
        }
        return res;
    }

    public ViewTO loadView(final String name) {
        final ViewTO view = new ViewTO();
        getJdbcTemplate().query(VIEW_QUERY, new Object[] { name }, new RowCallbackHandler() {

            public void processRow(final ResultSet rs) throws SQLException {
                view.setName(rs.getString("view_name"));
                view.setSql(rs.getString("text"));
                view.setComment(StringUtils.defaultString(rs.getString("comments")));
            }

        });

        getJdbcTemplate().query(VIEW_COLUMNS_QUERY, new Object[] { name }, new RowCallbackHandler() {

            public void processRow(final ResultSet rs) throws SQLException {
                ColumnTO column = new ColumnTO();
                column.name = rs.getString("column_name");
                column.type = rs.getString("data_type");
                column.optional = BooleanUtils.toBoolean(rs.getString("optional"), "Y", "N");
                column.defval = StringUtils.defaultString(rs.getString("data_default"));
                column.comment = StringUtils.defaultString(rs.getString("comments"));

                view.addColumn(column);
            }
        });

        return view;
    }

}
